﻿// Kingdee.K3.FIN.IV.App.Report.XXIVDetailReportService
using Kingdee.BOS;
using Kingdee.BOS.App;
using Kingdee.BOS.App.Data;
using Kingdee.BOS.Contracts;
using Kingdee.BOS.Contracts.Report;
using Kingdee.BOS.Core.Enums;
using Kingdee.BOS.Core.Report;
using Kingdee.BOS.Orm.DataEntity;
using Kingdee.BOS.Resource;
using Kingdee.BOS.Util;
using Kingdee.K3.FIN.App.Core;
using Kingdee.K3.FIN.App.Core.ARAP.AbstractReport;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Linq;
using System.Text;

namespace K.KDL
{
    [Description("销项发票明细表扩展，加字段与过滤条件")]
    [HotUpdate]
    public class XXIVDetailReportServiceCustomExt : SysReportBaseService
    {
        private bool isIncludeUnAudit;

        private bool isByBill;

        public override void Initialize()
        {
            base.Initialize();
            base.ReportProperty.BillKeyFieldName = "FID";
            base.ReportProperty.FormIdFieldName = "FFORMID";
            base.ReportProperty.IsGroupSummary = true;
            List<DecimalControlField> list = new List<DecimalControlField>();
            list.Add(new DecimalControlField("FPRICEDIGITS", "FAUXPRICE"));
            list.Add(new DecimalControlField("FPRICEDIGITS", "FAUXTAXPRICE"));
            list.Add(new DecimalControlField("FDIGITSFOR", "FDISCOUNTAMOUNTFOR"));
            list.Add(new DecimalControlField("FDIGITSFOR", "FNOTAXAMOUNTFOR"));
            list.Add(new DecimalControlField("FDIGITSFOR", "FTAXAMOUNTFOR"));
            list.Add(new DecimalControlField("FDIGITSFOR", "FAMOUNTFOR"));
            list.Add(new DecimalControlField("FDIGITS", "FDISCOUNTAMOUNT"));
            list.Add(new DecimalControlField("FDIGITS", "FNOTAXAMOUNT"));
            list.Add(new DecimalControlField("FDIGITS", "FTAXAMOUNT"));
            list.Add(new DecimalControlField("FDIGITS", "FAMOUNT"));
            list.Add(new DecimalControlField("FQTYDIGITS", "FPRICEQTY"));
            base.ReportProperty.DecimalControlFieldList = list;
            base.ReportProperty.IsGroupSummary = true;
            base.ReportProperty.DspInsteadColumnsInfo.DefaultDspInsteadColumns.Add("FMaterialID", "FMaterialName");
        }

        public override ReportTitles GetReportTitles(IRptParams filter)
        {
            ReportTitles reportTitles = new ReportTitles();
            DynamicObject customFilter = filter.FilterParameter.CustomFilter;
            string orgs = customFilter["SettleOrgIdList"].ToString();
            string orgsName = ReportCommonFunc.GetOrgsName(orgs, base.Context);
            reportTitles.AddTitle("FSettleOrg_H", orgsName);
            string baseDataName = ReportCommonFunc.GetBaseDataName(customFilter, "Affiliation");
            reportTitles.AddTitle("FAffiliation_H", baseDataName);
            string arg = ResManager.LoadKDString("至", "0032360000023881", SubSystemType.FIN);
            string arg2 = Convert.ToDateTime(customFilter["StartDate"]).ToString("yyyy-MM-dd");
            string arg3 = Convert.ToDateTime(customFilter["EndDate"]).ToString("yyyy-MM-dd");
            string sTitleValue = string.Format("{0} {1} {2}", arg2, arg, arg3);
            reportTitles.AddTitle("FDateScope_H", sTitleValue);
            return reportTitles;
        }

        public override ReportHeader GetReportHeaders(IRptParams filter)
        {
            base.GetReportHeaders(filter);
            ReportHeader reportHeader = new ReportHeader();

            var index = 0;
            reportHeader.AddChild("FIDENTITYID", new LocaleValue(ResManager.LoadKDString("序列号", "0032360000023882", SubSystemType.FIN)), index++);
            reportHeader.AddChild("FCONTACTUNITNUMBER", new LocaleValue(ResManager.LoadKDString("往来单位编码", "0032360000023883", SubSystemType.FIN)), index++);
            reportHeader.AddChild("FCONTACTUNITNAME", new LocaleValue(ResManager.LoadKDString("往来单位名称", "0032360000023884", SubSystemType.FIN)), index++);
            reportHeader.AddChild("FBILLTYPENAME", new LocaleValue(ResManager.LoadKDString("单据类型", "0032360000023885", SubSystemType.FIN)), index++);
            reportHeader.AddChild("FBILLNO", new LocaleValue(ResManager.LoadKDString("单据编号", "0032360000023886", SubSystemType.FIN)), index++);

            //修改
            reportHeader.AddChild("FIVCODE", new LocaleValue("发票代码", this.Context.UserLocale.LCID), index++);
            reportHeader.AddChild("FIVNUMBER", new LocaleValue("发票号码", this.Context.UserLocale.LCID), index++);

            reportHeader.AddChild("FDATE", new LocaleValue(ResManager.LoadKDString("业务日期", "0032360000021411", SubSystemType.FIN)), index++);
            reportHeader.AddChild("FINVOICEDATE", new LocaleValue(ResManager.LoadKDString("发票日期", "0032360000021408", SubSystemType.FIN)), index++);
            reportHeader.AddChild("FBUSINESSDEPTNAME", new LocaleValue(ResManager.LoadKDString("销售部门", "003246000003316", SubSystemType.FIN)), index++);
            reportHeader.AddChild("FSALEERANME", new LocaleValue("销售员", this.Context.UserLocale.LCID), index++);
            reportHeader.AddChild("F_PATC_XSXZ", new LocaleValue("销售性质", this.Context.UserLocale.LCID), index++);

            if (!this.isByBill)
            {
                reportHeader.AddChild("FMATERIALID", new LocaleValue(ResManager.LoadKDString("物料", "0032360000022905", SubSystemType.FIN)), index++);
                reportHeader.AddChild("FSPECIFICATION", new LocaleValue("规格型号", this.Context.UserLocale.LCID), index++);
                reportHeader.AddChild("FMATERIALGROUPNAME", new LocaleValue(ResManager.LoadKDString("物料分组", "0032360000022906", SubSystemType.FIN)), index++);
                reportHeader.AddChild("FITEMNUMBER", new LocaleValue(ResManager.LoadKDString("项目编码", "0032360000023887", SubSystemType.FIN)), index++);
                reportHeader.AddChild("FITEMNAME", new LocaleValue(ResManager.LoadKDString("项目名称", "0032360000023888", SubSystemType.FIN)), index++);
                reportHeader.AddChild("FPRICEUNITNAME", new LocaleValue(ResManager.LoadKDString("计价单位", "0032360000023889", SubSystemType.FIN)), index++);
                var temp1 = reportHeader.AddChild("FPRICEQTY", new LocaleValue(ResManager.LoadKDString("计价数量", "0032360000023890", SubSystemType.FIN)), SqlStorageType.SqlDecimal, true);
                temp1.ColIndex = index++;
                var temp2 = reportHeader.AddChild("FAUXPRICE", new LocaleValue(ResManager.LoadKDString("单价", "0032360000023891", SubSystemType.FIN)), SqlStorageType.SqlDecimal, true);
                temp2.ColIndex = index++;
                var temp3 = reportHeader.AddChild("FAUXTAXPRICE", new LocaleValue(ResManager.LoadKDString("含税单价", "0032360000023892", SubSystemType.FIN)), SqlStorageType.SqlDecimal, true);
                temp3.ColIndex = index++;
                var temp4 = reportHeader.AddChild("FTAXRATE", new LocaleValue(ResManager.LoadKDString("税率", "0032360000023893", SubSystemType.FIN)), SqlStorageType.SqlDecimal, true);
                temp4.ColIndex = index++;
                var temp5 = reportHeader.AddChild("FDISCOUNTRATE", new LocaleValue(ResManager.LoadKDString("折扣率", "0032360000023894", SubSystemType.FIN)), SqlStorageType.SqlDecimal, true);
                temp5.ColIndex = index++;
            }
            reportHeader.AddChild("FCURRENCYFORNAME", new LocaleValue(ResManager.LoadKDString("原币", "0032360000023897", SubSystemType.FIN)), index++);
            var temp6 = reportHeader.AddChild("FDISCOUNTAMOUNTFOR", new LocaleValue(ResManager.LoadKDString("折扣额", "0032360000023898", SubSystemType.FIN)), SqlStorageType.SqlDecimal, true);
            temp6.ColIndex = index++;
            var temp7 = reportHeader.AddChild("FNOTAXAMOUNTFOR", new LocaleValue(ResManager.LoadKDString("不含税金额", "0032360000023899", SubSystemType.FIN)), SqlStorageType.SqlDecimal, true);
            temp7.ColIndex = index++;
            var temp8 = reportHeader.AddChild("FTAXAMOUNTFOR", new LocaleValue(ResManager.LoadKDString("税额", "0032360000023900", SubSystemType.FIN)), SqlStorageType.SqlDecimal, true);
            temp8.ColIndex = index++;
            var temp9 = reportHeader.AddChild("FAMOUNTFOR", new LocaleValue(ResManager.LoadKDString("价税合计", "0032360000023901", SubSystemType.FIN)), SqlStorageType.SqlDecimal, true);
            temp9.ColIndex = index++;
            reportHeader.AddChild("FCURRENCYNAME", new LocaleValue(ResManager.LoadKDString("本位币", "0032360000023903", SubSystemType.FIN)), index++);
            var temp10 = reportHeader.AddChild("FDISCOUNTAMOUNT", new LocaleValue(ResManager.LoadKDString("折扣额本位币", "0032360000023904", SubSystemType.FIN)), SqlStorageType.SqlDecimal, true);
            temp10.ColIndex = index++;
            var temp11 = reportHeader.AddChild("FNOTAXAMOUNT", new LocaleValue(ResManager.LoadKDString("不含税金额本位币", "0032360000023905", SubSystemType.FIN)), SqlStorageType.SqlDecimal, true);
            temp11.ColIndex = index++;
            var temp12 = reportHeader.AddChild("FTAXAMOUNT", new LocaleValue(ResManager.LoadKDString("税额本位币", "0032360000023906", SubSystemType.FIN)), SqlStorageType.SqlDecimal, true);
            temp12.ColIndex = index++;
            var temp13 = reportHeader.AddChild("FAMOUNT", new LocaleValue(ResManager.LoadKDString("价税合计本位币", "0032360000023907", SubSystemType.FIN)), SqlStorageType.SqlDecimal, true);
            temp13.ColIndex = index++;

            return reportHeader;
        }

        public override List<SummaryField> GetSummaryColumnInfo(IRptParams filter)
        {
            List<SummaryField> list = new List<SummaryField>();
            if (!this.isByBill)
            {
                list.Add(new SummaryField("FPRICEQTY", BOSEnums.Enu_SummaryType.SUM));
            }
            list.Add(new SummaryField("FDISCOUNTAMOUNTFOR", BOSEnums.Enu_SummaryType.SUM));
            list.Add(new SummaryField("FNOTAXAMOUNTFOR", BOSEnums.Enu_SummaryType.SUM));
            list.Add(new SummaryField("FTAXAMOUNTFOR", BOSEnums.Enu_SummaryType.SUM));
            list.Add(new SummaryField("FAMOUNTFOR", BOSEnums.Enu_SummaryType.SUM));
            list.Add(new SummaryField("FDISCOUNTAMOUNT", BOSEnums.Enu_SummaryType.SUM));
            list.Add(new SummaryField("FNOTAXAMOUNT", BOSEnums.Enu_SummaryType.SUM));
            list.Add(new SummaryField("FTAXAMOUNT", BOSEnums.Enu_SummaryType.SUM));
            list.Add(new SummaryField("FAMOUNT", BOSEnums.Enu_SummaryType.SUM));
            return list;
        }

        public override void BuilderReportSqlAndTempTable(IRptParams filter, string tableName)
        {
            base.BuilderReportSqlAndTempTable(filter, tableName);
            using (new SessionScope())
            {
                DynamicObject customFilter = filter.FilterParameter.CustomFilter;
                this.isIncludeUnAudit = Convert.ToBoolean(customFilter["IncludeUnAudit"]);  //包括未审核单据
                this.isByBill = Convert.ToBoolean(customFilter["ByBill"]); //按单据显示
                string text = DBUtils.CreateSessionTemplateTable(base.Context, "TM_IV_JXDETAILRPT_DTL", this.CreateDetailTableSql());
                this.GetCGInvoiceData(filter, text);
                this.GetEXPInvoiceData(filter, text);
                string text2 = DBUtils.CreateSessionTemplateTable(base.Context, "TM_IV_JXDETAILRPT_SUM", this.CreateSumTableSql());
                if (this.isByBill)
                {
                    this.GetSumTempTableData(text, text2);
                }
                string sortString = filter.FilterParameter.SortString;
                if (!this.isByBill)
                {
                    this.GetReportData_Dtl(tableName, text, sortString);
                }
                else
                {
                    this.GetReportData_Sum(tableName, text2, sortString);
                }
            }
        }

        private string CreateDetailTableSql()
        {
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.AppendLine(" (");
            stringBuilder.AppendLine("   FID                   INT NOT NULL DEFAULT 0,");
            stringBuilder.AppendLine("   FFORMID               NVARCHAR(255) NOT NULL DEFAULT ' ',");
            stringBuilder.AppendLine("   FCONTACTUNITNUMBER    NVARCHAR(255) NOT NULL DEFAULT ' ',");
            stringBuilder.AppendLine("   FCONTACTUNITNAME      NVARCHAR(255) NOT NULL DEFAULT ' ',");
            stringBuilder.AppendLine("   FBILLTYPENAME         NVARCHAR(255) NOT NULL DEFAULT ' ',");
            stringBuilder.AppendLine("   FBILLNO               NVARCHAR(255) NOT NULL DEFAULT ' ',");
            stringBuilder.AppendLine("   FDATE                 DATETIME NOT NULL DEFAULT GETDATE(),");
            stringBuilder.AppendLine("   FINVOICEDATE          DATETIME NOT NULL DEFAULT GETDATE(),");
            stringBuilder.AppendLine("   FBUSINESSDEPTNAME     NVARCHAR(255) NOT NULL  DEFAULT ' ',");
            stringBuilder.AppendLine("   FENTRYID              INT NOT NULL DEFAULT 0,");
            stringBuilder.AppendLine("   FMATERIALID           INT NOT NULL DEFAULT 0,");
            stringBuilder.AppendLine("   FITEMNUMBER           NVARCHAR(255) NOT NULL DEFAULT ' ',");
            stringBuilder.AppendLine("   FITEMNAME             NVARCHAR(255) NOT NULL DEFAULT ' ',");
            stringBuilder.AppendLine("   FPRICEUNITNAME        NVARCHAR(30) NOT NULL DEFAULT ' ',");
            stringBuilder.AppendLine("   FPRICEQTY             DECIMAL(23,10) NOT NULL DEFAULT 0,");
            stringBuilder.AppendLine("   FAUXPRICE             DECIMAL(23,10) NOT NULL DEFAULT 0,");
            stringBuilder.AppendLine("   FAUXTAXPRICE          DECIMAL(23,10) NOT NULL DEFAULT 0,");
            stringBuilder.AppendLine("   FTAXRATE              DECIMAL(23,10) NOT NULL DEFAULT 0,");
            stringBuilder.AppendLine("   FDISCOUNTRATE         DECIMAL(23,10) NOT NULL DEFAULT 0,");
            stringBuilder.AppendLine("   FISDEDUCT             CHAR(1) NOT NULL DEFAULT '0',");
            stringBuilder.AppendLine("   FDEDUCTDATE           DATETIME NOT NULL DEFAULT GETDATE(),");
            stringBuilder.AppendLine("   FCURRENCYFORNAME      NVARCHAR(50) NOT NULL DEFAULT ' ',");
            stringBuilder.AppendLine("   FDISCOUNTAMOUNTFOR    DECIMAL(23,10) NOT NULL DEFAULT 0,");
            stringBuilder.AppendLine("   FNOTAXAMOUNTFOR       DECIMAL(23,10) NOT NULL DEFAULT 0,");
            stringBuilder.AppendLine("   FTAXAMOUNTFOR         DECIMAL(23,10) NOT NULL DEFAULT 0,");
            stringBuilder.AppendLine("   FAMOUNTFOR            DECIMAL(23,10) NOT NULL DEFAULT 0,");
            stringBuilder.AppendLine("   FDEDUCTTAXFOR         DECIMAL(23,10) NOT NULL DEFAULT 0,");
            stringBuilder.AppendLine("   FCURRENCYNAME         NVARCHAR(50) NOT NULL DEFAULT ' ',");
            stringBuilder.AppendLine("   FDISCOUNTAMOUNT       DECIMAL(23,10) NOT NULL DEFAULT 0,");
            stringBuilder.AppendLine("   FNOTAXAMOUNT          DECIMAL(23,10) NOT NULL DEFAULT 0,");
            stringBuilder.AppendLine("   FTAXAMOUNT            DECIMAL(23,10) NOT NULL DEFAULT 0,");
            stringBuilder.AppendLine("   FAMOUNT               DECIMAL(23,10) NOT NULL DEFAULT 0,");
            stringBuilder.AppendLine("   FDEDUCTTAX            DECIMAL(23,10) NOT NULL DEFAULT 0,");
            stringBuilder.AppendLine("   FDIGITSFOR            INT NOT NULL DEFAULT 2,");
            stringBuilder.AppendLine("   FDIGITS               INT NOT NULL DEFAULT 2,");
            stringBuilder.AppendLine("   FPRICEDIGITS          INT NOT NULL DEFAULT 4,");
            stringBuilder.AppendLine("   FQTYDIGITS            INT NOT NULL DEFAULT 4,");  //修改，
            stringBuilder.AppendLine("   FIVCODE        NVARCHAR(255) DEFAULT ' ',");  //NOT NULL 发票代码
            stringBuilder.AppendLine("   FIVNUMBER      NVARCHAR(255) DEFAULT ' ',");  //NOT NULL 发票号码
            stringBuilder.AppendLine("   FSALEERANME      NVARCHAR(255) DEFAULT ' ',");  // 销售员， FBUSINESSDEPTNAME
            stringBuilder.AppendLine("   FSPECIFICATION      NVARCHAR(255) DEFAULT ' ',");  // 物料规格型号， FMATERIALID
            stringBuilder.AppendLine("   F_PATC_XSXZ      NVARCHAR(255) DEFAULT ' '");
            stringBuilder.AppendLine(" )");
            return stringBuilder.ToString();
        }

        private string CreateSumTableSql()
        {
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.AppendLine(" (");
            stringBuilder.AppendLine("   FID                   INT NOT NULL DEFAULT 0,");
            stringBuilder.AppendLine("   FFORMID               NVARCHAR(255) NOT NULL DEFAULT ' ',");
            stringBuilder.AppendLine("   FCONTACTUNITNUMBER    NVARCHAR(255) NOT NULL DEFAULT ' ',");
            stringBuilder.AppendLine("   FCONTACTUNITNAME      NVARCHAR(255) NOT NULL DEFAULT ' ',");
            stringBuilder.AppendLine("   FBILLTYPENAME         NVARCHAR(255) NOT NULL DEFAULT ' ',");
            stringBuilder.AppendLine("   FBILLNO               NVARCHAR(255) NOT NULL DEFAULT ' ',");
            stringBuilder.AppendLine("   FDATE                 DATETIME NOT NULL DEFAULT GETDATE(),");
            stringBuilder.AppendLine("   FINVOICEDATE          DATETIME NOT NULL DEFAULT GETDATE(),");
            stringBuilder.AppendLine("   FBUSINESSDEPTNAME     NVARCHAR(255) NOT NULL  DEFAULT ' ',");
            stringBuilder.AppendLine("   FCURRENCYFORNAME      NVARCHAR(50) NOT NULL DEFAULT ' ',");
            stringBuilder.AppendLine("   FDISCOUNTAMOUNTFOR    DECIMAL(23,10) NOT NULL DEFAULT 0,");
            stringBuilder.AppendLine("   FNOTAXAMOUNTFOR       DECIMAL(23,10) NOT NULL DEFAULT 0,");
            stringBuilder.AppendLine("   FTAXAMOUNTFOR         DECIMAL(23,10) NOT NULL DEFAULT 0,");
            stringBuilder.AppendLine("   FAMOUNTFOR            DECIMAL(23,10) NOT NULL DEFAULT 0,");
            stringBuilder.AppendLine("   FDEDUCTTAXFOR         DECIMAL(23,10) NOT NULL DEFAULT 0,");
            stringBuilder.AppendLine("   FCURRENCYNAME         NVARCHAR(50) NOT NULL DEFAULT ' ',");
            stringBuilder.AppendLine("   FDISCOUNTAMOUNT       DECIMAL(23,10) NOT NULL DEFAULT 0,");
            stringBuilder.AppendLine("   FNOTAXAMOUNT          DECIMAL(23,10) NOT NULL DEFAULT 0,");
            stringBuilder.AppendLine("   FTAXAMOUNT            DECIMAL(23,10) NOT NULL DEFAULT 0,");
            stringBuilder.AppendLine("   FAMOUNT               DECIMAL(23,10) NOT NULL DEFAULT 0,");
            stringBuilder.AppendLine("   FDEDUCTTAX            DECIMAL(23,10) NOT NULL DEFAULT 0,");
            stringBuilder.AppendLine("   FDIGITSFOR            INT NOT NULL DEFAULT 2,");
            stringBuilder.AppendLine("   FDIGITS               INT NOT NULL DEFAULT 2,");
            stringBuilder.AppendLine("   FPRICEDIGITS          INT NOT NULL DEFAULT 4,");
            stringBuilder.AppendLine("   FQTYDIGITS            INT NOT NULL DEFAULT 4,");  //修改
            stringBuilder.AppendLine("   FIVCODE        NVARCHAR(255)  DEFAULT ' ',");  //NOT NULL
            stringBuilder.AppendLine("   FIVNUMBER      NVARCHAR(255)  DEFAULT ' ',");  //NOT NULL
            stringBuilder.AppendLine("   FSALEERANME      NVARCHAR(255) DEFAULT ' ',");  // 销售员, 规格型号与物料保持一致
            stringBuilder.AppendLine("   F_PATC_XSXZ      NVARCHAR(255) DEFAULT ' '");
            stringBuilder.AppendLine(" )");
            return stringBuilder.ToString();
        }

        private void GetCGInvoiceData(IRptParams filter, string tmpTableName)
        {
            int lCID = base.Context.UserLocale.LCID;
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.AppendFormat("Insert into {0} ", tmpTableName);
            stringBuilder.AppendLine("(FID,FFORMID,FCONTACTUNITNUMBER,FCONTACTUNITNAME,FBILLTYPENAME,FBILLNO,FDATE,FINVOICEDATE,FBUSINESSDEPTNAME,");
            stringBuilder.AppendLine(" FENTRYID,FMATERIALID,FITEMNUMBER,FITEMNAME,FPRICEUNITNAME,FPRICEQTY,FAUXPRICE,FAUXTAXPRICE,FTAXRATE,FDISCOUNTRATE,");
            stringBuilder.AppendLine(" FCURRENCYFORNAME,FDISCOUNTAMOUNTFOR,FNOTAXAMOUNTFOR,FTAXAMOUNTFOR,FAMOUNTFOR,");
            stringBuilder.AppendLine(" FCURRENCYNAME,FDISCOUNTAMOUNT,FNOTAXAMOUNT,FTAXAMOUNT,FAMOUNT,FDIGITSFOR,FPRICEDIGITS,FDIGITS,FQTYDIGITS, FIVCODE, FIVNUMBER, FSALEERANME, FSPECIFICATION, F_PATC_XSXZ)"); //修改
            stringBuilder.AppendLine("select sal.FID,sal.FFORMID,con.FNumber as FCONTACTUNITNUMBER,isnull(conl.FName,' ') as FCONTACTUNITNAME,bill.Fname as FBILLTYPENAME,sal.FBILLNO,sal.FDATE,sal.FINVOICEDATE,isnull(DEPT1.FNAME,' ') AS FBUSINESSDEPTNAME,");
            stringBuilder.AppendLine("sale.FENTRYID,sale.FMATERIALID,mat.FNUMBER as FITEMNUMBER,isnull( matl.FNAME,' ') as FITEMNAME,isnull(unitl.FNAME,' ') as FPRICEUNITNAME,sale.FPRICEQTY,sale.FAUXPRICE,sale.FAUXTAXPRICE,sale.FTAXRATE,sale.FENTRYDISCOUNTRATE as FDISCOUNTRATE,");
            stringBuilder.AppendLine("curfl.fName as FCURRENCYFORNAME,sale.FDISCOUNTAMOUNTFOR,sale.FAMOUNTFOR as FNOTAXAMOUNTFOR,sale.FDETAILTAXAMOUNTFOR as FTAXAMOUNTFOR,sale.FALLAMOUNTFOR as FAMOUNTFOR,");
            stringBuilder.AppendLine("curl.fName as FCURRENCYNAME,salo.FDISCOUNTAMOUNT,salo.FNOTAXAMOUNT,salo.FDETAILTAXAMOUNT as FTAXAMOUNT,salo.FALLAMOUNT as FAMOUNT,curf.FAmountDigits as FDIGITSFOR,curf.FPRICEDIGITS,cur.FAmountDigits as FDIGITS, ISNULL(unit.FPRECISION,0) "); 
            stringBuilder.AppendLine(" , ISNULL(salo2.FIVCODE, ''), ISNULL(salo2.FIVNUMBER, ''), isnull(saller.FNAME,' ') AS FSALEERANME, isnull(matl.FSPECIFICATION, '') AS FSPECIFICATION, isnull(CDSL2.FDATAVALUE, '') as F_PATC_XSXZ "); //修改
            stringBuilder.AppendLine(string.Format("from {0} sal", "T_IV_SALESIC"));
            stringBuilder.AppendLine(string.Format("left join {0} salo2 on sal.FID=salo2.FID", "T_IV_SALESIC_O"));  //修改
            stringBuilder.AppendLine(string.Format("left join {0} sale on sale.FID = sal.FID", "T_IV_SALESICENTRY"));
            stringBuilder.AppendLine(string.Format("left join {0} salo on salo.FENTRYID = sale.FENTRYID", "T_IV_SALESICENTRY_O"));
            stringBuilder.AppendLine(string.Format("left JOIN {0} con ON con.fitemid = sal.FCUSTOMERID and con.fformid = 'BD_Customer' ", "V_FIN_CONTACTTYPE"));
            stringBuilder.AppendLine(string.Format("left JOIN {0} conl ON conl.fitemid = con.fitemid and conl.FLOCALEID = {1} ", "V_FIN_CONTACTTYPE_l", lCID));
            stringBuilder.AppendLine(string.Format("left join {0} bill on bill.FBilltypeid = sal.FBillTypeID and bill.FlocaleID = {1}", "t_bas_billtype_l", lCID));
            stringBuilder.AppendLine(string.Format("left join {0} curf on curf.FCurrencyID = sal.FCurrencyID", "t_bd_currency"));
            stringBuilder.AppendLine(string.Format("left join {0} curfl on curfl.FCurrencyID = sal.FCurrencyID and curfl.Flocaleid = {1}", "t_bd_currency_l", lCID));
            stringBuilder.AppendLine(string.Format("left join {0} cur on cur.FCurrencyID = sal.FMAINBOOKSTDCURRID", "t_bd_currency"));
            stringBuilder.AppendLine(string.Format("left join {0} curl on curl.FCurrencyID = sal.FMAINBOOKSTDCURRID and curl.Flocaleid = {1}", "t_bd_currency_l", lCID));
            stringBuilder.AppendLine(string.Format("left join {0} mat  on sale.FMATERIALID = mat.FMATERIALID  ", "t_bd_Material"));
            stringBuilder.AppendLine(string.Format("left join {0} matl  on sale.FMATERIALID = matl.FMATERIALID and matl.FLOCALEID={1} ", "t_bd_Material_l", lCID));
            stringBuilder.AppendLine(string.Format("left join {0} unit  on sale.FPRICEUNITID =unit.FUNITID  ", "T_BD_UNIT"));
            stringBuilder.AppendLine(string.Format("left join {0} unitl  on sale.FPRICEUNITID =unitl.FUNITID and unitl.FLOCALEID={1} ", "T_BD_UNIT_L", lCID));
            stringBuilder.AppendFormat("LEFT JOIN {0} DEPT1 ON sal.FSALEDEPTID=DEPT1.FDEPTID AND DEPT1.FLOCALEID={1} ", "T_BD_DEPARTMENT_L", lCID);
            stringBuilder.AppendFormat("LEFT JOIN {0} saller ON sal.FSALEERID=saller.FID AND saller.FLOCALEID={1} ", "V_BD_SALESMAN_L", lCID);
            stringBuilder.AppendFormat("left join {0}  CC2 on sal.FCUSTOMERID=CC2.FCUSTID ", "T_BD_CUSTOMER");
            stringBuilder.AppendFormat("LEFT JOIN {0} CDSL2 ON CDSL2.FENTRYID=CC2.F_PATC_XSXZ and CDSL2.FLOCALEID={1} ", "T_BAS_ASSISTANTDATAENTRY_L", lCID);
            stringBuilder.AppendLine(this.GetWhereSql(filter, "sal", false, true));
            DBUtils.Execute(base.Context, stringBuilder.ToString());
        }

        private void GetEXPInvoiceData(IRptParams filter, string tmpTableName)
        {
            DynamicObject _ = filter.FilterParameter.CustomFilter;
            int lCID = base.Context.UserLocale.LCID;
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.AppendFormat("Insert into {0} ", tmpTableName);
            stringBuilder.AppendLine("(FID,FFORMID,FCONTACTUNITNUMBER,FCONTACTUNITNAME,FBILLTYPENAME,FBILLNO,FDATE,FINVOICEDATE,FBUSINESSDEPTNAME,");
            stringBuilder.AppendLine(" FENTRYID,FITEMNUMBER,FITEMNAME,FPRICEUNITNAME,FPRICEQTY,FAUXPRICE,FAUXTAXPRICE,FTAXRATE,FDISCOUNTRATE,");
            stringBuilder.AppendLine(" FCURRENCYFORNAME,FDISCOUNTAMOUNTFOR,FNOTAXAMOUNTFOR,FTAXAMOUNTFOR,FAMOUNTFOR,");
            stringBuilder.AppendLine(" FCURRENCYNAME,FDISCOUNTAMOUNT,FNOTAXAMOUNT,FTAXAMOUNT,FAMOUNT,FDIGITSFOR,FPRICEDIGITS,FDIGITS,FQTYDIGITS, FIVCODE, FIVNUMBER, FSALEERANME, F_PATC_XSXZ)");  //修改
            stringBuilder.AppendLine("select sal.FID,sal.FFORMID,con.FNumber as FCONTACTUNITNUMBER,isnull(conl.FName,' ') as FCONTACTUNITNAME,bill.Fname as FBILLTYPENAME,sal.FBILLNO,sal.FDATE,sal.FINVOICEDATE,isnull(DEPT1.FNAME,' ') AS FBUSINESSDEPTNAME,");
            stringBuilder.AppendLine("sale.FENTRYID,exp.FNUMBER as FITEMNUMBER,isnull(expl.FNAME,' ') as FITEMNAME,isnull(unitl.FNAME,' ') as FPRICEUNITNAME,sale.FPRICEQTY,sale.FAUXPRICE,sale.FAUXTAXPRICE,sale.FTAXRATE,0 as FDISCOUNTRATE,");
            stringBuilder.AppendLine("curfl.fName as FCURRENCYFORNAME,0 as FDISCOUNTAMOUNTFOR,sale.FAMOUNTFOR as FNOTAXAMOUNTFOR,sale.FDETAILTAXAMOUNTFOR as FTAXAMOUNTFOR,sale.FALLAMOUNTFOR as FAMOUNTFOR,");
            stringBuilder.AppendLine("curl.fName as FCURRENCYNAME,0 as FDISCOUNTAMOUNT,sale.FNOTAXAMOUNT,sale.FDETAILTAXAMOUNT as FTAXAMOUNT,sale.FALLAMOUNT as FAMOUNT,curf.FAmountDigits as FDIGITSFOR,curf.FPRICEDIGITS,cur.FAmountDigits as FDIGITS, ISNULL(unit.FPRECISION,0) ");  //修改
            stringBuilder.AppendLine(" , ISNULL(sal.FIVCODE, ''), ISNULL(sal.FIVNUMBER, '') , isnull(saller.FNAME,' ') AS FSALEERANME,  '' as F_PATC_XSXZ "); //修改
            stringBuilder.AppendLine(string.Format("from {0} sal", "T_IV_SALEEXINV"));
            stringBuilder.AppendLine(string.Format("inner join {0} sale on sale.FID = sal.FID", "T_IV_SALEEXINVENTRY"));
            stringBuilder.AppendLine(string.Format("LEFT JOIN {0} con ON con.fitemid = sal.FCONTACTUNIT and con.fformid = sal.FCONTACTUNITTYPE ", "V_FIN_CONTACTTYPE"));
            stringBuilder.AppendLine(string.Format("LEFT JOIN {0} conl ON conl.fitemid = con.fitemid and conl.FLOCALEID = {1} ", "V_FIN_CONTACTTYPE_l", lCID));
            stringBuilder.AppendLine(string.Format("left join {0} bill on bill.FBilltypeid = sal.FBillTypeID and bill.FlocaleID = {1}", "t_bas_billtype_l", lCID));
            stringBuilder.AppendLine(string.Format("left join {0} curf on curf.FCurrencyID = sal.FCurrencyID", "t_bd_currency"));
            stringBuilder.AppendLine(string.Format("left join {0} curfl on curfl.FCurrencyID = sal.FCurrencyID and curfl.Flocaleid = {1}", "t_bd_currency_l", lCID));
            stringBuilder.AppendLine(string.Format("left join {0} cur on cur.FCurrencyID = sal.FMAINBOOKSTDCURRID", "t_bd_currency"));
            stringBuilder.AppendLine(string.Format("left join {0} curl on curl.FCurrencyID = sal.FMAINBOOKSTDCURRID and curl.Flocaleid = {1}", "t_bd_currency_l", lCID));
            stringBuilder.AppendLine(string.Format("left join {0} exp  on sale.FEXPENSEID = exp.FEXPID  ", "T_BD_EXPENSE"));
            stringBuilder.AppendLine(string.Format("left join {0} expl  on sale.FEXPENSEID = expl.FEXPID and expl.FLOCALEID={1} ", "T_BD_EXPENSE_L", lCID));
            stringBuilder.AppendLine(string.Format("left join {0} unit  on sale.FPRICEUNITID =unit.FUNITID  ", "T_BD_UNIT"));
            stringBuilder.AppendLine(string.Format("left join {0} unitl  on sale.FPRICEUNITID =unitl.FUNITID and unitl.FLOCALEID={1} ", "T_BD_UNIT_L", lCID));
            stringBuilder.AppendFormat("LEFT JOIN {0} DEPT1 ON sal.FSALEDEPTID=DEPT1.FDEPTID AND DEPT1.FLOCALEID={1} ", "T_BD_DEPARTMENT_L", lCID);
            stringBuilder.AppendFormat("LEFT JOIN {0} saller ON sal.FSALEERID=saller.FID AND saller.FLOCALEID={1} ", "V_BD_SALESMAN_L", lCID);
            //stringBuilder.AppendFormat("left join {0}  CC2 on sal.FCUSTOMERID=CC2.FCUSTID ", "T_BD_CUSTOMER");
            //stringBuilder.AppendFormat("LEFT JOIN {0} CDSL2 ON CDSL2.FENTRYID=CC2.F_PATC_XSXZ and CDSL2.FLOCALEID={1} ", "T_BAS_ASSISTANTDATAENTRY_L", lCID);
            stringBuilder.AppendLine(this.GetWhereSql(filter, "sal", true, false));
            DBUtils.Execute(base.Context, stringBuilder.ToString());
        }

        private string GetWhereSql(IRptParams filter, string tableAlias, bool IsEXP, bool getCGInvoiceData)
        {
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.Append("where 1=1");
            if (!IsEXP)
            {
                stringBuilder.AppendFormat(" and {0}.fcancelstatus = 'A'", tableAlias);
            }
            if (!this.isIncludeUnAudit)
            {
                stringBuilder.AppendFormat(" and {0}.FDocumentStatus = 'C'", tableAlias);
            }
            else
            {
                stringBuilder.AppendFormat(" and {0}.FDocumentStatus <> 'Z'", tableAlias);
            }
            string filterString = filter.FilterParameter.FilterString;
            if (!filterString.IsNullOrEmptyOrWhiteSpace())
            {
                filterString = filterString.Replace("FCONTACTUNITNUMBER", "con.FNumber");
                filterString = filterString.Replace("FCONTACTUNITNAME", "conl.FName");
                filterString = filterString.Replace("FCURRENCYFORNAME", "curfl.fName");
                filterString = filterString.Replace("FBILLTYPENAME", "bill.Fname");
                filterString = filterString.Replace("FDATE", string.Format("{0}.FDATE", tableAlias));
                filterString = filterString.Replace("FINVOICEDATE", string.Format("{0}.FINVOICEDATE", tableAlias));
                filterString = filterString.Replace("FBUSINESSDEPTNAME", "DEPT1.FNAME");

                if (getCGInvoiceData)
                {
                    filterString = filterString.Replace("FIVCODE", "salo2.FIVCODE");
                    filterString = filterString.Replace("FIVNUMBER", "salo2.FIVNUMBER");
                } else
                {
                    filterString = filterString.Replace("FIVCODE", "sal.FIVCODE");
                    filterString = filterString.Replace("FIVNUMBER", "sal.FIVNUMBER");
                }

                stringBuilder.AppendFormat(" and {0}", filterString);
            }
            DynamicObject customFilter = filter.FilterParameter.CustomFilter;
            if (customFilter == null)
            {
                return stringBuilder.ToString();
            }
            string text = customFilter["SettleOrgIdList"].IsNullOrEmptyOrWhiteSpace() ? string.Empty : customFilter["SettleOrgIdList"].ToString();
            long num = customFilter["Affiliation_Id"].IsNullOrEmptyOrWhiteSpace() ? 0 : Convert.ToInt64(customFilter["Affiliation_Id"]);
            if (num != 0)
            {
                IOrganizationService service = ServiceHelper.GetService<IOrganizationService>();
                List<long> orgsInAffiliation = service.GetOrgsInAffiliation(base.Context, Convert.ToInt64(text), num);
                List<long> permissionOrgIds = CommonFunction.GetPermissionOrgIdList(base.Context, base.BusinessInfo.GetForm().Id, "6e44119a58cb4a8e86f6c385e14a17ad");
                orgsInAffiliation = orgsInAffiliation.FindAll((long o) => permissionOrgIds.Contains(o));
                stringBuilder.AppendFormat(" and {0}.FSettleOrgID in ({1})", tableAlias, string.Join(",", orgsInAffiliation));
            }
            else if (!text.IsNullOrEmptyOrWhiteSpace())
            {
                stringBuilder.AppendFormat(" and {0}.FSettleOrgID in ({1})", tableAlias, text);
            }
            string text2 = Convert.ToString(customFilter["ContactUnitType"]);
            if (!string.IsNullOrWhiteSpace(text2))
            {
                stringBuilder.AppendFormat(" and con.fformid = '{0}'", text2);
            }
            string baseDataNumber = ReportCommonFunc.GetBaseDataNumber(customFilter, "ContactUnitFrom");
            string baseDataNumber2 = ReportCommonFunc.GetBaseDataNumber(customFilter, "ContactUnitTo");
            if (!string.IsNullOrWhiteSpace(baseDataNumber))
            {
                stringBuilder.AppendFormat(" and con.FNumber >= '{0}'", baseDataNumber);
            }
            if (!string.IsNullOrWhiteSpace(baseDataNumber2))
            {
                stringBuilder.AppendFormat(" and con.FNumber <= '{0}'", baseDataNumber2);
            }
            DynamicObjectCollection source = customFilter["CurrencyIdList"] as DynamicObjectCollection;
            if (source.Count() > 0)
            {
                stringBuilder.AppendFormat(" and {0}.FCurrencyID in ({1})", tableAlias, string.Join(",", (from currencyId in source
                                                                                                          select currencyId["CurrencyIdList_Id"].ToString()).ToList().ToArray()));
            }
            string arg = "{ts'" + Convert.ToDateTime(customFilter["StartDate"]).ToString("yyyy-MM-dd 00:00:00") + "'}";
            string arg2 = "{ts'" + Convert.ToDateTime(customFilter["EndDate"]).ToString("yyyy-MM-dd 23:59:59") + "'}";
            stringBuilder.AppendFormat(" and {0}.FDate >= {1} and {0}.FDate <= {2}", tableAlias, arg, arg2);
            return stringBuilder.ToString();
        }

        private void GetSumTempTableData(string detailTempTableName, string sumTempTableName)
        {
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.AppendFormat("Insert into {0} ", sumTempTableName);
            stringBuilder.AppendLine("(FID,FFORMID,FCONTACTUNITNUMBER,FCONTACTUNITNAME,FBILLTYPENAME,FBILLNO,FDATE,FINVOICEDATE,FBUSINESSDEPTNAME,");
            stringBuilder.AppendLine(" FCURRENCYFORNAME,FDISCOUNTAMOUNTFOR,FNOTAXAMOUNTFOR,FTAXAMOUNTFOR,FAMOUNTFOR,");
            stringBuilder.AppendLine(" FCURRENCYNAME,FDISCOUNTAMOUNT,FNOTAXAMOUNT,FTAXAMOUNT,FAMOUNT,FDIGITSFOR,FPRICEDIGITS,FDIGITS,FQTYDIGITS, FIVCODE, FIVNUMBER, FSALEERANME, F_PATC_XSXZ)");  //修改
            stringBuilder.AppendLine("select FID,FFORMID,FCONTACTUNITNUMBER,FCONTACTUNITNAME,FBILLTYPENAME,FBILLNO,FDATE,FINVOICEDATE,FBUSINESSDEPTNAME,");
            stringBuilder.AppendLine("max(FCURRENCYFORNAME),sum(FDISCOUNTAMOUNTFOR),sum(FNOTAXAMOUNTFOR),sum(FTAXAMOUNTFOR),sum(FAMOUNTFOR),");
            stringBuilder.AppendLine("max(FCURRENCYNAME),sum(FDISCOUNTAMOUNT),sum(FNOTAXAMOUNT),sum(FTAXAMOUNT),sum(FAMOUNT),MAX(FDIGITSFOR),MAX(FPRICEDIGITS),MAX(FDIGITS),MAX(FQTYDIGITS), MAX(FIVCODE), MAX(FIVNUMBER), MAX(FSALEERANME), MAX(F_PATC_XSXZ) ");  //修改, group中不加
            stringBuilder.AppendLine(string.Format(" from {0} temp", detailTempTableName));
            stringBuilder.AppendLine("group by FID,FFORMID,FCONTACTUNITNUMBER,FCONTACTUNITNAME,FBILLTYPENAME,FBILLNO,FDATE,FINVOICEDATE,FBUSINESSDEPTNAME ");
            DBUtils.Execute(base.Context, stringBuilder.ToString());
        }

        private void GetReportData_Dtl(string tableName, string tempTableName, string sortString)
        {
            string arg = sortString;
            if (string.IsNullOrWhiteSpace(sortString))
            {
                arg = "T1.FCONTACTUNITNUMBER asc,T1.FCURRENCYFORNAME asc,T1.FBILLTYPENAME asc,T1.FDATE asc,T1.FINVOICEDATE asc";
            }
            base.KSQL_SEQ = string.Format(base.KSQL_SEQ, arg);
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.AppendLine(string.Format("select {0},", base.KSQL_SEQ));
            stringBuilder.AppendLine("T1.FID,T1.FFORMID,T1.FCONTACTUNITNUMBER,T1.FCONTACTUNITNAME,T1.FBILLTYPENAME,T1.FBILLNO,TO_CHAR(T1.FDATE,'YYYY-MM-DD') as FDATE,TO_CHAR(T1.FINVOICEDATE,'YYYY-MM-DD') as FINVOICEDATE,");
            stringBuilder.AppendLine("T1.FBUSINESSDEPTNAME,T1.FENTRYID,T1.FMATERIALID,isnull(M2.FNAME,' ') as FMaterialName,ISNULL(mg1.fname,' ') as FMATERIALGROUPNAME,T1.FITEMNUMBER,T1.FITEMNAME,T1.FPRICEUNITNAME,T1.FPRICEQTY,T1.FAUXPRICE,T1.FAUXTAXPRICE,T1.FTAXRATE,T1.FDISCOUNTRATE,");
            stringBuilder.AppendLine("T1.FCURRENCYFORNAME,T1.FDISCOUNTAMOUNTFOR,T1.FNOTAXAMOUNTFOR,T1.FTAXAMOUNTFOR,T1.FAMOUNTFOR,");
            stringBuilder.AppendLine("T1.FCURRENCYNAME,T1.FDISCOUNTAMOUNT,T1.FNOTAXAMOUNT,T1.FTAXAMOUNT,T1.FAMOUNT,T1.FDIGITSFOR,T1.FPRICEDIGITS,T1.FDIGITS,T1.FQTYDIGITS ,T1.FIVCODE,T1.FIVNUMBER, T1.FSALEERANME, T1.FSPECIFICATION, T1.F_PATC_XSXZ ");  //修改
            stringBuilder.AppendLine(string.Format("into {0} from {1} T1 ", tableName, tempTableName));
            stringBuilder.AppendLine(" LEFT JOIN T_BD_MATERIAL M1 on M1.FMaterialID=T1.FMaterialID ");
            stringBuilder.AppendFormat(" LEFT JOIN T_BD_MATERIAL_L M2 on M1.FMaterialID=M2.FMaterialID and M2.FLOCALEID={0} ", base.Context.UserLocale.LCID);
            stringBuilder.AppendFormat(" LEFT JOIN T_BD_MATERIALGROUP_L mg1 on M1.FMATERIALGROUP=mg1.FID and mg1.flocaleid={0} ", base.Context.UserLocale.LCID);
            DBUtils.Execute(base.Context, stringBuilder.ToString());
        }

        private void GetReportData_Sum(string tableName, string tempTableName, string sortString)
        {
            string arg = sortString;
            if (string.IsNullOrWhiteSpace(sortString))
            {
                arg = "FCONTACTUNITNUMBER asc,FCURRENCYFORNAME asc,FBILLTYPENAME asc,FDATE asc,FINVOICEDATE asc";
            }
            base.KSQL_SEQ = string.Format(base.KSQL_SEQ, arg);
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.AppendLine(string.Format("select {0},", base.KSQL_SEQ));
            stringBuilder.AppendLine("FID,FFORMID,FCONTACTUNITNUMBER,FCONTACTUNITNAME,FBILLTYPENAME,FBILLNO,TO_CHAR(FDATE,'YYYY-MM-DD') as FDATE,TO_CHAR(FINVOICEDATE,'YYYY-MM-DD') as FINVOICEDATE,");
            stringBuilder.AppendLine("FBUSINESSDEPTNAME,FCURRENCYFORNAME,FDISCOUNTAMOUNTFOR,FNOTAXAMOUNTFOR,FTAXAMOUNTFOR,FAMOUNTFOR,");
            stringBuilder.AppendLine("FCURRENCYNAME,FDISCOUNTAMOUNT,FNOTAXAMOUNT,FTAXAMOUNT,FAMOUNT,FDIGITSFOR,FPRICEDIGITS,FDIGITS,FQTYDIGITS ,FIVCODE ,FIVNUMBER, FSALEERANME, F_PATC_XSXZ "); //修改
            stringBuilder.AppendLine(string.Format("into {0} from {1} ", tableName, tempTableName));
            DBUtils.Execute(base.Context, stringBuilder.ToString());
        }
    }
}
